﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PUCIT.eFCS.DataAccess;
using System.Data.Linq;
using System.Web;
using System.IO;

namespace PUCIT.eFCS.Business
{
    public class BusinessClass
    {
        public bool isVerified(String email)
        {
            int i = email.IndexOf('@');
            String temp = email.Substring(i + 1, 5);
            if (temp.Equals("pucit") || temp.Equals("PUCIT"))
                return true;
            else
                return false;
        }

        public int AddUserWindows(String name, String email)
        {
            UserEntries obj = new UserEntries();
            String cName = null;

            if (name.Equals("Aaban"))
            {
                cName = "admin";
            }
            else if (name.Equals("Principal"))
            {
                cName = "principal";
            }
            else if (name.Equals("DS"))
            {
                cName = "ds";
            }
            else if (name.Equals("SAC"))
            {
                cName = "sac";
            }
            else if (name.Equals("AC"))
            {
                cName = "ac";
            }


            if (!obj.UserExistsWindows(name))
            {
                email = null;
                int cId = obj.GetCategoryId(cName);
                if (cId == 7) { cId = 1; }
                obj.AddUsers(name, email, cId);
                return cId;
            }
            else
            {
                int cId = obj.GetCategoryId(cName);
                if (cId == 7) { cId = 1; }
                return cId;

            }
        }
        public int AddUser(String name, String email)
        {
            UserEntries obj = new UserEntries();
            if (!obj.UserExists(email))
            {
                String cName = email.Substring(0, 3);
                int cId = obj.GetCategoryId(cName);
                if (cId == 7) { cId = 1; }
                obj.AddUsers(name, email, cId);
                int uId = obj.GetUserId(email);
                return cId;
            }
            else
            {
                if (email.IndexOf("gmail") > 0)
                {
                    int atIndex = email.IndexOf('@');
                    String cName = email.Substring(4, atIndex - 4);
                    int cId = obj.GetCategoryId(cName);
                    return cId;
                }
                else
                {
                    return 6;
                    /* String cName = email.Substring(0, 3);
                     int cId = obj.GetCategoryId(cName);
                     if (cId == 7) { cId = 1; }
                     return cId; */
                }
            }

        }
        public List<string> GetAllCategoriesNames()
        {
            UserEntries obj = new UserEntries();
            List<string> categoryNameList = obj.SelectAllCategoryNames();

            return categoryNameList;
        }
        public int GetCategoryIdByCategoryName(string categoryName)
        {
            UserEntries obj = new UserEntries();
            int cid = obj.GetCategoryIdByCategoryName(categoryName);
            return cid;
        }
        public String GetCategoryName(int cId)
        {
            return (new UserEntries()).GetCategoryName(cId);
        }

        public string GetAbbreviation(string name)
        {
            string abbreviation = "";
            string[] parts = name.Split(' ');

            for (int i = 0; i < parts.Length; i++)
                abbreviation += parts[i][0];

            return abbreviation;
        }

        public CirculationItem GetCirculationItem(Circulation circulation)
        {
            CirculationItem circulationItem = new CirculationItem();

            circulationItem.CirculationId = circulation.CirculationId;
            circulationItem.FormType = GetAbbreviation(GetDocumentNameByCirculationId(circulation.CirculationId));
            circulationItem.Initiater = circulation.Initiater;
            circulationItem.SubmissionDate = circulation.SubmissionDate.ToShortDateString();
            circulationItem.SubmissionTime = circulation.SubmissionDate.ToLongTimeString();
            circulationItem.Progress = circulation.Progress + "%";
            circulationItem.CurrentStation = circulation.CurrentStation;

            return circulationItem;
        }

        public int SelectUserId(string userName, string password)
        {
            int userId = -1;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var userIdResults = dataContext.uspSelectUsertype(userName, password);

                foreach (uspSelectUsertypeResult userIdResult in userIdResults)
                {
                    userId = userIdResult.usertypeId;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return userId;
        }

        public int GetCirculationDocumentId(int circulationId)
        {
            int documentId = -1;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentIdResults = dataContext.uspSelectDocIdByFormId(circulationId);

                foreach (uspSelectDocIdByFormIdResult documentIdResult in documentIdResults)
                {
                    documentId = documentIdResult.docId;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentId;
        }

        public List<int> GetFieldIdList(int sectionId)
        {
            List<int> fieldIdList = new List<int>();
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var fieldIdResults = dataContext.uspSelectDocfieldIdBySecId(sectionId);

                foreach (uspSelectDocfieldIdBySecIdResult fieldIdResult in fieldIdResults)
                {
                    fieldIdList.Add(fieldIdResult.fieldId);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return fieldIdList;
        }

        public int CalculateProgress(int documentId, int currentSectionId)
        {
            int progress = 0;
            int sectionCount = 0, count = 0;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var sectionIdResults = dataContext.uspSelectAllSecIdByDocId(documentId);

                foreach (uspSelectAllSecIdByDocIdResult sectionIdResult in sectionIdResults)
                {
                    sectionCount++;
                    if (sectionIdResult.secId == currentSectionId)
                    {
                        count = sectionCount;
                    }
                }

                progress = (int)(Math.Ceiling(((float)count / sectionCount) * 100));

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return progress;
        }

        public string GetUsername(int userId)
        {
            string userName = "";
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var userResults = dataContext.uspSelectUserNameByUserId(userId); // is ka alternative name nhi hai doc me

                foreach (uspSelectUserNameByUserIdResult userResult in userResults)
                {
                    userName = userResult.username;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return userName;
        }

        public string GetUsernameWindows(int userId)
        {
            return new UserEntries().GetUserNameByUserId(userId);
        }


        public string GetCategoryUsername(int userId)
        {
            UserEntries obj = new UserEntries();

            string userName = GetCategoryName(userId);

            return userName;
        }

        /* public string GetUsernameByEmail(String email)
         {
             string userName = "";
             eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

             try
             {
                 dataContext.Connection.Open();
                 dataContext.Transaction = dataContext.Connection.BeginTransaction();

                 var userResults = dataContext.uspSelectUserNameByUserId(userId);

                 foreach (uspSelectUserNameByUserIdResult userResult in userResults)
                 {
                     userName = userResult.userName;
                 }

                 dataContext.SubmitChanges();
                 dataContext.Transaction.Commit();
             }
             catch (Exception)
             {
                 dataContext.Transaction.Rollback();
             }
             finally
             {
                 dataContext.Connection.Close();
                 dataContext.Transaction = null;
             }

             return userName;
         }
         */

        public int GetDocumentId(string documentName, string documentVersion)
        {
            int documentId = 0;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentIdResults = dataContext.uspSelectDocIdByDocnameAndDocversion(documentName, documentVersion);

                foreach (uspSelectDocIdByDocnameAndDocversionResult documentIdResult in documentIdResults)
                {
                    documentId = documentIdResult.docId;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentId;
        }

        public string GetDocumentVersion(int circulationId)
        {
            string documentVersion = "";
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentIdResults = dataContext.uspSelectDocIdByFormId(circulationId);

                int docId = 0;

                foreach (uspSelectDocIdByFormIdResult documentIdResult in documentIdResults)
                {
                    docId = documentIdResult.docId;
                }

                var documentVersionResults = dataContext.uspSelectDocversionByDocId(docId); //  it was......no alternative name found in doc

                foreach (uspSelectDocversionByDocIdResult documentVersionResult in documentVersionResults)
                {
                    documentVersion = documentVersionResult.docversion;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentVersion;
        }
        /*
        public int GetUserIdByUserNameNew(string userName)
	{
	int userId = -1;
	eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

	try
	{
	dataContext.Connection.Open();
	dataContext.Transaction = dataContext.Connection.BeginTransaction();

	var userResults = dataContext.uspSelectUserIdByUsernameNew(userName);

	foreach (uspSelectUserIdByUsernameResult userResult in userResults)
	{
	userId = userResult.userId;
	}

	dataContext.SubmitChanges();
	dataContext.Transaction.Commit();
	}
	catch (Exception)
	{
	dataContext.Transaction.Rollback();
	}
	finally
	{
	dataContext.Connection.Close();
	dataContext.Transaction = null;
	}

	return userId;
	} 
        */


        public int GetUserIdByUserName(string userName)
        {
            int userId = -1;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            string b = userName;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var userResults = dataContext.uspSelectUsertypeIdBytitle(userName); // not confirmed here ... will check later "uspSelectUserIdByUsername"

                foreach (uspSelectUsertypeIdBytitleResult userResult in userResults)
                {
                    userId = userResult.usertypeId;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return userId;
        }

        public int GetUserIdByUserNameWindows(string userName)
        {
            return (new UserEntries()).GetUserIdByUsername(userName);
        }

        public int GetUserIdByEmail(string email)
        {
            return (new UserEntries()).GetUserId(email);

        }
        /*
        public int GetUserIdByEmail(string email)
        {
            
            int userId = -1;
            
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var userResults = dataContext.uspSelectUserIdByEmail(email);

                foreach (uspSelectUserIdByEmailResult userResult in userResults)
                {
                    userId = userResult.userId;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            
            return userId;
        }
        */
        public string GetEmailByUserId(int categoryId)
        {
            string email = "";
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var emailResults = dataContext.uspSelectEmailByUsertypeId(categoryId);

                foreach (uspSelectEmailByUsertypeIdResult emailResult in emailResults)
                {
                    email = emailResult.email;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return email;
        }

        public string GetDocumentNameByCirculationId(int circulationId)
        {
            int documentId = GetCirculationDocumentId(circulationId);
            string documentName = "";

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentNameResults = dataContext.uspSelectDocnameByDocId(documentId);

                foreach (uspSelectDocnameByDocIdResult documentNameResult in documentNameResults)
                {
                    documentName = documentNameResult.docname;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentName;
        }

        public int GetNextSectionId(int sectionId)
        {
            int nextSectionId = -1;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var nextSectionIdResults = dataContext.uspSelectNextSectionIdBySecId(sectionId);

                foreach (uspSelectNextSectionIdBySecIdResult nextSectionIdResult in nextSectionIdResults)
                {
                    nextSectionId = nextSectionIdResult.nextSectionId;
                    break;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return nextSectionId;
        }

        public int GetSectionUserId(int sectionId, string category)
        {
            int sectionUserId = -1;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                // select category Id instead
                var sectionUserIdResults = dataContext.uspSelectUsertypeIdBySecIdAndCategory(sectionId, category);

                foreach (uspSelectUsertypeIdBySecIdAndCategoryResult sectionUserIdResult in sectionUserIdResults)
                {
                    sectionUserId = sectionUserIdResult.usertypeId;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return sectionUserId;
        }

        public List<string> GetFieldHtmlIdList(int sectionId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<string> FieldHtmlIdList = new List<string>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var htmlFieldIdResults = dataContext.uspSelectAllHtmlFieldIdBySecId(sectionId);

                foreach (uspSelectAllHtmlFieldIdBySecIdResult htmlFieldIdResult in htmlFieldIdResults)
                {
                    FieldHtmlIdList.Add(htmlFieldIdResult.htmlFieldId);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return FieldHtmlIdList;
        }

        //getting id of html fields of profile form
        public List<string> GetFieldHtmlIdListOfProfileAttr()
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<string> FieldHtmlIdList = new List<string>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var htmlFieldIdResults = dataContext.uspSelectAllProfileAttributes();

                foreach (uspSelectAllProfileAttributesResult htmlFieldIdResult in htmlFieldIdResults)
                {
                    FieldHtmlIdList.Add(htmlFieldIdResult.fieldId);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return FieldHtmlIdList;
        }

        public Document GenerateForm(int documentId, int circulationId, int categoryId) // Retrieves document sections untill, userId of a section matches with parameter 'userId' (Including that section)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            Document document = new Document();
            Section section;
            Field field;
            bool sectionUserFound = false;
            Section secAtt = new Section();
            List<Attachments> att = new List<Attachments>();
            att = getAttachments(circulationId);
            secAtt.SectionTitle = "Attachments";
            secAtt.attList = att;

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentResults = dataContext.uspSelectDocnameByDocId(documentId);

                foreach (uspSelectDocnameByDocIdResult documentResult in documentResults)
                {
                    document.DocumentName = documentResult.docname;
                }

                var documentVersionResults = dataContext.uspSelectDocversionByDocId(documentId);

                foreach (uspSelectDocversionByDocIdResult documentVersionResult in documentVersionResults)
                {
                    document.DocumentVersion = documentVersionResult.docversion;
                }

                document.DocumentSections = new List<Section>();

                var sectionResults = dataContext.uspSelectAllDocsectionByDocId(documentId);

                foreach (uspSelectAllDocsectionByDocIdResult sectionResult in sectionResults)
                {
                    int sectionCategoryId = GetSectionUserId(sectionResult.secId, "To");


                    // categoryId describes the category of loginned user, and sectionCategoryId describes the category of that particular section
                    if (sectionCategoryId == categoryId)
                        sectionUserFound = true;


                    section = new Section();
                    section.SectionTitle = sectionResult.title;
                    section.SectionFields = new List<Field>();

                    int i = 0;
                    var fieldResults = dataContext.uspSelectAllFieldsBySecId(sectionResult.secId);

                    foreach (uspSelectAllFieldsBySecIdResult fieldResult in fieldResults)
                    {
                        field = new Field();
                        field.FieldLabel = fieldResult.label;
                        field.FieldHtmlId = fieldResult.htmlFieldId;
                        field.FieldHtml = fieldResult.html;

                        if (circulationId != -1) // If its a circulation, then retrieve field values as well
                        {

                            var fieldValueResults = dataContext.uspSelectFieldValue(circulationId, sectionResult.secId, fieldResult.fieldId);

                            string value;

                            foreach (uspSelectFieldValueResult fieldValueResult in fieldValueResults)
                            {
                                field.FieldValue = fieldValueResult.value;
                                value = field.FieldValue;


                                //if (!sectionUserFound && field.FieldLabel.Equals("Attachment: "))
                                //{
                                //    string filepath = value;
                                //    if (filepath.IndexOf("fakepath") > 0)
                                //    {
                                //        string[] filename = filepath.Split('\\');
                                //        filepath = filename[filename.Length - 1];
                                //        string fn = filepath;
                                //        filepath = circulationId + "^" + sectionCategoryId; // +"^" + filepath;

                                //        field.FieldHtml = "<a href=Attachments/" + filepath + "/" + fn + ">" + fn + "</a>";
                                //    }
                                //}
                            }

                        }
                        section.SectionFields.Add(field);
                        i++;
                    }

                    section.SectionId = sectionResult.secId;
                    section.UserId = sectionCategoryId;

                    /// file attachment code
                    if (att.Count > 0 && document.DocumentSections.Count >= 1)
                    {
                        document.DocumentSections.Add(secAtt);
                    }
                    ///

                    document.DocumentSections.Add(section);

                    if (sectionUserFound)
                        break;
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return document;
        }

        public Document GenerateFormTemplate(int documentId) // Retrieves document sections untill, userId of a section matches with parameter 'userId' (Including that section)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            Document document = new Document();
            Section section;
            Field field;

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentResults = dataContext.uspSelectDocnameByDocId(documentId);

                foreach (uspSelectDocnameByDocIdResult documentResult in documentResults)
                {
                    document.DocumentName = documentResult.docname;
                }

                document.DocumentSections = new List<Section>();

                var sectionResults = dataContext.uspSelectAllDocsectionByDocId(documentId);

                foreach (uspSelectAllDocsectionByDocIdResult sectionResult in sectionResults)
                {
                    int sectionUserId = GetSectionUserId(sectionResult.secId, "To");

                    section = new Section();
                    section.SectionTitle = sectionResult.title;
                    section.SectionFields = new List<Field>();

                    var fieldResults = dataContext.uspSelectAllFieldsBySecId(sectionResult.secId);

                    foreach (uspSelectAllFieldsBySecIdResult fieldResult in fieldResults)
                    {
                        field = new Field();
                        field.FieldLabel = fieldResult.label;
                        field.FieldHtmlId = fieldResult.htmlFieldId;
                        field.FieldHtml = fieldResult.html;
                        field.SeqNo = fieldResult.sequenceNo;

                        section.SectionFields.Add(field);
                    }

                    section.SectionId = sectionResult.secId;
                    section.UserId = sectionUserId;
                    section.SeqNo = sectionResult.sequenceNo;
                    document.DocumentSections.Add(section);

                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return document;
        }

        public Circulation GetCirculationDetails(int circulationId, bool isDocumentRequired, int userId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            Circulation circulation = new Circulation();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var circulationResults = dataContext.uspSelectFormByFormId(circulationId);

                foreach (uspSelectFormByFormIdResult circulationResult in circulationResults)
                {
                    circulation.CirculationId = circulationResult.formId;
                    circulation.SubmissionDate = circulationResult.submitionDate;
                    circulation.Progress = circulationResult.progress;
                    circulation.Initiater = GetUsername(circulationResult.userId);
                    circulation.CurrentStation = GetCategoryUsername(circulationResult.currentstation);

                    if (isDocumentRequired)
                    {
                        if (userId == -1 && !circulation.Progress.Equals("100")) // View Circulation Details of an Incomplete Circulation
                        {
                            circulation.Document = GenerateForm(circulationResult.docId, circulationId, circulationResult.currentstation); // Retrieve form sections till Current Station's section
                            circulation.Document.DocumentSections.RemoveAt(circulation.Document.DocumentSections.Count - 1); // Remove the last section of 'Current Station'
                        }

                        else // View Circulation Details & Append Station User's Section (Edit Circulation Details) 'OR' View Circulation Details of a Complete Circulation
                        {
                            circulation.Document = GenerateForm(circulationResult.docId, circulationId, userId);
                        }
                    }
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return circulation;
        }

        public Circulation GetPrintPreview(int circulationId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            Circulation circulation = new Circulation();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var circulationResults = dataContext.uspSelectFormByFormId(circulationId);

                foreach (uspSelectFormByFormIdResult circulationResult in circulationResults)
                {
                    circulation.CirculationId = circulationResult.formId;
                    circulation.SubmissionDate = circulationResult.submitionDate;
                    circulation.Progress = circulationResult.progress;
                    // ???????????????????????? usertype id or user id???

                    circulation.Initiater = GetUsername(circulationResult.userId);
                    circulation.CurrentStation = GetUsername(circulationResult.currentstation);


                    circulation.Document = GenerateForm(circulationResult.docId, circulationResult.formId, -1);
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return circulation;
        }

        public int SaveSectionValues(int documentId, int sectionId, int userId, List<string> fieldValueList)
        {
            int circulationId = 0;
            int progress = CalculateProgress(documentId, sectionId);
            int x = GetNextSectionId(sectionId);
            int currentStationId = GetSectionUserId(GetNextSectionId(sectionId), "To");
            DateTime submissionDateTime = DateTime.Now;

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                dataContext.uspInsertFormTableRow(documentId, userId, submissionDateTime, progress.ToString(), currentStationId);
                var formIdResults = dataContext.uspSelectFormId(documentId, userId, submissionDateTime);

                foreach (uspSelectFormIdResult formIdResult in formIdResults)
                {
                    circulationId = formIdResult.formId;
                }

                List<int> fieldIdList = GetFieldIdList(sectionId);

                for (int i = 0; i < fieldIdList.Count; i++)
                {
                    string filepath = fieldValueList[i];
                    if (filepath.IndexOf("fakepath") > 0)
                    {
                        /*                        string[] filename = filepath.Split('\\');
                                                filepath = filename[filename.Length -1];
                                                string fn = circulationId + "_" + sectionId + "_" + userId + "_" + filepath;
                                                string SaveLocation = "~\\Attachments" + "\\" + fn;
                                                filepath = fn;
                                                */
                    }
                    dataContext.uspInsertFormfieldTableRow(circulationId, fieldIdList[i], sectionId, filepath);


                }


                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return circulationId;
        }

        public void SaveStationUserSectionValues(int circulationId, int documentId, int sectionId, int userId, List<string> fieldValueList)
        {
            int progress = CalculateProgress(documentId, sectionId);
            int currentStationId = GetSectionUserId(GetNextSectionId(sectionId), "To");

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                dataContext.uspUpdateProgressAndCurrentStationByFormId(circulationId, progress.ToString(), currentStationId);

                List<int> fieldIdList = GetFieldIdList(sectionId);

                for (int i = 0; i < fieldIdList.Count; i++)
                {
                    dataContext.uspInsertFormfieldTableRow(circulationId, fieldIdList[i], sectionId, fieldValueList[i]);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
        }

        public void delSpecificFields(List<int> fieldIds)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                for (int i = 0; i < fieldIds.Count; i++)
                {
                    dataContext.uspDeleteDocfieldTableRowByFieldId(fieldIds[i]);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
        }

        public List<CirculationItem> GetCirculationList(int userId, int documentId, int disableDocumentFilter, string progress, int disableProgressFilter)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<CirculationItem> circulationItemList = new List<CirculationItem>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var circulationIdResults = dataContext.uspSelectAllFilteredFormIdByUsertypeId(userId, documentId, disableDocumentFilter, progress, 1);

                if (disableProgressFilter == 1) // No Progress(Completion Status) Filter Applied
                {
                    foreach (uspSelectAllFilteredFormIdByUsertypeIdResult circulationIdResult in circulationIdResults)
                    {
                        CirculationItem circulationItem = GetCirculationItem(GetCirculationDetails(circulationIdResult.formId, false, -1));

                        string version = GetDocumentVersion(circulationIdResult.formId);

                        circulationItem.FormType += " " + version;

                        circulationItemList.Add(circulationItem);
                    }
                }
                else // Progress Filter Applied
                {
                    if (progress.Equals("Complete"))
                    {
                        foreach (uspSelectAllFilteredFormIdByUsertypeIdResult circulationIdResult in circulationIdResults)
                        {
                            Circulation circulation = GetCirculationDetails(circulationIdResult.formId, false, -1);

                            if (circulation.Progress.Equals("100"))
                            {
                                CirculationItem circulationItem = GetCirculationItem(circulation);

                                string version = GetDocumentVersion(circulationIdResult.formId);

                                circulationItem.FormType += " " + version;

                                circulationItemList.Add(circulationItem);

                            }
                        }
                    }
                    else // Incomplete
                    {
                        foreach (uspSelectAllFilteredFormIdByUsertypeIdResult circulationIdResult in circulationIdResults)
                        {
                            Circulation circulation = GetCirculationDetails(circulationIdResult.formId, false, -1);

                            if (!circulation.Progress.Equals("100"))
                            {
                                CirculationItem circulationItem = GetCirculationItem(circulation);

                                string version = GetDocumentVersion(circulationIdResult.formId);

                                circulationItem.FormType += " " + version;

                                circulationItemList.Add(circulationItem);
                            }
                        }
                    }
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return circulationItemList;
        }

        public List<DocumentItem> GetDocumentList()
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<DocumentItem> documentItemList = new List<DocumentItem>();
            List<DocumentItem> documentItemUpdatedList = new List<DocumentItem>();
            float maxVersion = 0.0F;
            int index = -1;
            bool check = false;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentItemResults = dataContext.uspSelectAllCompleteDocuments();

                DocumentItem documentItem;
                DocumentItem documentItemNext;

                foreach (uspSelectAllCompleteDocumentsResult documentItemResult in documentItemResults)
                {
                    documentItem = new DocumentItem();
                    documentItem.DocumentId = documentItemResult.docId;
                    documentItem.DocumentName = documentItemResult.docname;
                    documentItem.DocumentVersion = documentItemResult.docversion;
                    documentItemList.Add(documentItem);
                }
                for (int i = 0; i < documentItemList.Count; i++)
                {
                    documentItem = documentItemList[i];
                    maxVersion = float.Parse(documentItem.DocumentVersion);

                    for (int j = 1; j < documentItemList.Count; j++)
                    {
                        documentItemNext = documentItemList[j];

                        if (documentItem.DocumentName == documentItemNext.DocumentName)
                        {
                            check = true;
                            if (float.Parse(documentItemNext.DocumentVersion) > maxVersion)
                            {
                                maxVersion = float.Parse(documentItemNext.DocumentVersion);
                                index = j;
                            }
                            else
                            {
                                index = i;
                            }
                        }
                    }
                    if (!check)
                    {
                        documentItem = new DocumentItem();
                        documentItem = documentItemList[i];
                        documentItemUpdatedList.Add(documentItem);

                    }
                    else
                    {
                        documentItem = new DocumentItem();
                        documentItem = documentItemList[index];
                        bool flag = true;
                        for (int k = 0; k < documentItemUpdatedList.Count; k++)
                        {
                            if (documentItem.DocumentName == documentItemUpdatedList[k].DocumentName)
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            documentItemUpdatedList.Add(documentItem);
                        }

                    }
                }

            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentItemUpdatedList;
        }

        public List<DocumentItem> GetIncompleteDocumentList()
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<DocumentItem> documentItemList = new List<DocumentItem>();
            List<DocumentItem> documentItemUpdatedList = new List<DocumentItem>();
            float maxVersion = 0.0F;
            int index = -1;
            bool check = false;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentItemResults = dataContext.uspSelectAllIncompleteDocuments();

                DocumentItem documentItem;
                DocumentItem documentItemNext;

                foreach (uspSelectAllIncompleteDocumentsResult documentItemResult in documentItemResults)
                {
                    documentItem = new DocumentItem();
                    documentItem.DocumentId = documentItemResult.docId;
                    documentItem.DocumentName = documentItemResult.docname;
                    documentItem.DocumentVersion = documentItemResult.docversion;
                    documentItemList.Add(documentItem);
                }
                for (int i = 0; i < documentItemList.Count; i++)
                {
                    documentItem = documentItemList[i];
                    maxVersion = float.Parse(documentItem.DocumentVersion);

                    for (int j = 1; j < documentItemList.Count; j++)
                    {
                        documentItemNext = documentItemList[j];

                        if (documentItem.DocumentName == documentItemNext.DocumentName)
                        {
                            check = true;
                            if (float.Parse(documentItemNext.DocumentVersion) > maxVersion)
                            {
                                maxVersion = float.Parse(documentItemNext.DocumentVersion);
                                index = j;
                            }
                            else
                            {
                                index = i;
                            }
                        }
                    }
                    if (!check)
                    {
                        documentItem = new DocumentItem();
                        documentItem = documentItemList[i];
                        documentItemUpdatedList.Add(documentItem);

                    }
                    else
                    {
                        documentItem = new DocumentItem();
                        documentItem = documentItemList[index];
                        bool flag = true;
                        for (int k = 0; k < documentItemUpdatedList.Count; k++)
                        {
                            if (documentItem.DocumentName == documentItemUpdatedList[k].DocumentName)
                            {
                                flag = false;
                                break;
                            }
                        }
                        if (flag)
                        {
                            documentItemUpdatedList.Add(documentItem);
                        }

                    }
                }

            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentItemUpdatedList;
        }

        public List<CirculationItem> GetToDoList(int categoryId, int documentId, int disableDocumentFilter)
        {
            //Replace UserId with CategoryId in Parameter
            int userId = categoryId;
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<CirculationItem> circulationItemList = new List<CirculationItem>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var circulationIdResults = dataContext.uspSelectAllFilteredFormIdByCurrentStationId(userId, documentId, disableDocumentFilter);

                foreach (uspSelectAllFilteredFormIdByCurrentStationIdResult circulationIdResult in circulationIdResults)
                {
                    CirculationItem circulationItem = GetCirculationItem(GetCirculationDetails(circulationIdResult.formId, false, -1));

                    string version = GetDocumentVersion(circulationIdResult.formId);

                    circulationItem.FormType += " " + version;

                    circulationItemList.Add(circulationItem);
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return circulationItemList;
        }

        public List<CirculationItem> GetCompletedCirculationList(int userId, int documentId, int disableDocumentFilter)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<CirculationItem> circulationItemList = new List<CirculationItem>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var circulationIdResults = dataContext.uspSelectAllFilteredFormsByProgress(userId, documentId, disableDocumentFilter);

                foreach (uspSelectAllFilteredFormsByProgressResult circulationIdResult in circulationIdResults)
                {
                    CirculationItem circulationItem = GetCirculationItem(GetCirculationDetails(circulationIdResult.formId, false, -1));

                    string version = GetDocumentVersion(circulationIdResult.formId);

                    circulationItem.FormType += " " + version;

                    circulationItemList.Add(circulationItem);
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return circulationItemList;
        }

        public List<string> GetUserForms(int userId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<string> formList = new List<string>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var formResults = dataContext.uspSelectUserForms(userId);

                foreach (uspSelectUserFormsResult formResult in formResults)
                {
                    formList.Add(formResult.docname + "/" + formResult.docversion + "," + formResult.docId); // e.g. General Request Form,1
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return formList;
        }

        public List<string> GetStationUserFormNames(int userId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<string> formNameList = new List<string>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var formNameResults = dataContext.uspSelectStationUserForms(userId);

                foreach (uspSelectStationUserFormsResult formNameResult in formNameResults)
                {
                    formNameList.Add(formNameResult.docname);
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return formNameList;
        }

        public void UpdateDocument(int docId, Boolean isComplete)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                dataContext.uspUpdateDocumentTableRowByDocIdNew(docId, isComplete);

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

        }

        public int InsertNewDocument(string FormName, string version, Boolean isComplete)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            int documentid = 0;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                //dataContext.uspInsertDocumentTableRow(FormName, version);
                dataContext.uspInsertDocumentTableRowNew(FormName, version, isComplete, 1);

                var documentidResults = dataContext.uspSelectDocIdByDocname(FormName);

                foreach (uspSelectDocIdByDocnameResult documentIdResult in documentidResults)
                {
                    documentid = documentIdResult.docId;
                }
                //var doctid = dataContext.uspSelectDocumentIdByDocumentName(FormName);
                //documentid = Convert.ToInt32(doctid);
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return documentid;
        }
        public int InsertNewDocument(string formName, string version, bool isComplete, int totalSections)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            int documentid = 0;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                //dataContext.uspInsertDocumentTableRow(FormName, version);
                dataContext.uspInsertDocumentTableRowNew(formName, version, isComplete, totalSections);

                var documentidResults = dataContext.uspSelectDocIdByDocname(formName);

                foreach (uspSelectDocIdByDocnameResult documentIdResult in documentidResults)
                {
                    documentid = documentIdResult.docId;
                }
                //var doctid = dataContext.uspSelectDocumentIdByDocumentName(FormName);
                //documentid = Convert.ToInt32(doctid);
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return documentid;
        }

        public int InsertNewDocument(string formName, string version, bool isComplete, int totalSections, int completedSections)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            int documentid = 0;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                //dataContext.uspInsertDocumentTableRow(FormName, version);
                // ????????????????????///
                //?????????????/// not in usp

                //  dataContext.uspInsertDocumentTableCompleteRow(formName, version, isComplete, totalSections, completedSections);

                var documentidResults = dataContext.uspSelectDocIdByDocname(formName);

                foreach (uspSelectDocIdByDocnameResult documentIdResult in documentidResults)
                {
                    documentid = documentIdResult.docId;
                }
                //var doctid = dataContext.uspSelectDocumentIdByDocumentName(FormName);
                //documentid = Convert.ToInt32(doctid);
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return documentid;
        }

        public int InsertNewDocument(string FormName, string version)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            int documentid = 0;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                dataContext.uspInsertDocumentTableRow(FormName, version);

                var documentidResults = dataContext.uspSelectDocIdByDocname(FormName);

                foreach (uspSelectDocIdByDocnameResult documentIdResult in documentidResults)
                {
                    documentid = documentIdResult.docId;
                }
                //var doctid = dataContext.uspSelectDocumentIdByDocumentName(FormName);
                //documentid = Convert.ToInt32(doctid);
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return documentid;
        }

        public int InsertSectionWithDocumentId(int documentid, int seqno, string title)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            int Sectionid = 0;
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                dataContext.uspInsertDocsectionTableRow(documentid, seqno, title);
                var SectionidResults = dataContext.uspSelectSecIdByDocIdAndTitle(documentid, title);

                foreach (uspSelectSecIdByDocIdAndTitleResult sectionIdResult in SectionidResults)
                {
                    Sectionid = sectionIdResult.secId;
                }
                //var sectid = dataContext.uspSelectSectionIdByDocumentIdAndTitle(documentid,title);
                //Sectionid = Convert.ToInt32(sectid);
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return Sectionid;
        }

        public void InsertFieldBySectionId(int sectionId, int seqno, string label, string htmlfieldId, string html)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                dataContext.uspInsertDocfieldTableRow(sectionId, seqno, label, htmlfieldId, html);

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
        }

        public void InsertNewAction(int sectionId, string label, string javascript, int nextSectionId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                dataContext.uspInsertActionTableRow(sectionId, label, javascript, nextSectionId);

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
        }

        public List<string> GetAllUserNames()
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<string> userNameList = new List<string>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var userNameResults = dataContext.uspSelectAllTitles();

                foreach (uspSelectAllTitlesResult userNameResult in userNameResults)
                {
                    userNameList.Add(userNameResult.title);
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return userNameList;
        }

        public void InsertSectionUserRow(int sectionId, int categoryId, string category)
        {
            UserEntries obj = new UserEntries();
            obj.InsertSectionUserRow(sectionId, categoryId, category);

            /*	eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
                try
                {
                    dataContext.Connection.Close();
                    dataContext.Connection.Open();
                    dataContext.Transaction = dataContext.Connection.BeginTransaction();

                    dataContext.uspInsertSecuserTableRow(sectionId, userId, category);

                    dataContext.SubmitChanges();
                    dataContext.Transaction.Commit();
                }
                catch (Exception)
                {
                    dataContext.Transaction.Rollback();
                }
                finally
                {
                    dataContext.Connection.Close();
                    dataContext.Transaction = null;
                } */
        }
        public string GetDocumentVersionByDocumentId(int documentId)
        {

            string documentVersion = "";

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentVersionResults = dataContext.uspSelectDocversionByDocId(documentId);

                foreach (uspSelectDocversionByDocIdResult documentVersionResult in documentVersionResults)
                {
                    documentVersion = documentVersionResult.docversion;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentVersion;
        }
        public string GetDocumentNameByDocumentId(int documentId)
        {

            string documentName = "";

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentNameResults = dataContext.uspSelectDocnameByDocId(documentId);

                foreach (uspSelectDocnameByDocIdResult documentNameResult in documentNameResults)
                {
                    documentName = documentNameResult.docname;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return documentName;
        }
        public void updateDocumentCompletedSection(int docId, int completedSections)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();


                dataContext.uspUpdateDocumentCompletedSections(docId, completedSections);


                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
        }
        public string GetDocumentTotalSectionsByDocumentId(int documentId)
        {
            string totalSections = "";

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentTotalSectionsResults = dataContext.uspSelectDocumentTotalSectionsByDocId(documentId);

                foreach (uspSelectDocumentTotalSectionsByDocIdResult documentTotalSectionsResult in documentTotalSectionsResults)
                {
                    totalSections = documentTotalSectionsResult.totalSections.ToString();
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return totalSections;
        }
        public string GetDocumentCompletedSectionsByDocumentId(int documentId)
        {
            string completedSections = "";

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var documentCompletedSectionsResults = dataContext.uspSelectDocumentCompletedSectionsByDocId(documentId);

                foreach (uspSelectDocumentCompletedSectionsByDocIdResult documentCompletedSectionsResult in documentCompletedSectionsResults)
                {
                    completedSections = documentCompletedSectionsResult.completedSections.ToString();
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return completedSections;
        }

        public string getUserProfilevalues(int attrId, int loginned_userId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            string value = "";
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var userprofiles = dataContext.uspselectAttrValueFromUserProf(attrId, loginned_userId);

                //value =  Convert.ToString(userprofiles);
                foreach (uspselectAttrValueFromUserProfResult userprofile in userprofiles)
                {
                    value = userprofile.attrvalue;
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return value;
        }
        // saving file attachments
        public void saveAttachments(Section att, int cirId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                foreach (Attachments item in att.attList)
                {
                    dataContext.uspSaveAttachments(cirId, att.SectionId, item.description, item.Path);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
        }

        // retreiving file attachments
        public List<Attachments> getAttachments(int cirId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<Attachments> attList = new List<Attachments>();
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                Attachments atch = new Attachments();
                var att = dataContext.uspGetAttachments(cirId);

                foreach (uspGetAttachmentsResult item in att)
                {
                    atch.description = item.Description;
                    atch.Path = item.path;
                    attList.Add(atch);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return attList;
        }


        //creating directory
        public void createDirectory(int userid)
        {
            string _directoryName = @"D:\efcs - version 1.8\eFCS\Uploads\";
            _directoryName += userid;

            // check folder exists
            if (Directory.Exists(_directoryName))
            {
                // ok, the directory exists
            }
            // if not then create
            else
            {
                Directory.CreateDirectory(_directoryName);
            }

        }

        // saving student after google authentication
        public void SaveStudent(string username, string fname, string email, DateTime dob, string semester, string rollno, string section, string session)
        {
            /*
             * 1 Name
             * 2 Section 
             * 3 Semester
             * 4 Roll no
             * 5 Father Name
             * 6 DOB
             * 7 Session
             * 8 Occupation ; student for now
             * 
             *  add email, usrname, usertype in users table.. get userId from users table 
             *  and add profile information in userprof acc to values in profattr table
             */
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                if (AddUser(username, email) > 0)
                {
                    int userId = GetUserIdByEmail(email);

                    dataContext.uspInsertAttrValueInUserProf(userId, 1, username);
                    dataContext.uspInsertAttrValueInUserProf(userId, 2, section);
                    dataContext.uspInsertAttrValueInUserProf(userId, 3, semester);
                    dataContext.uspInsertAttrValueInUserProf(userId, 4, rollno);
                    dataContext.uspInsertAttrValueInUserProf(userId, 5, fname);
                    dataContext.uspInsertAttrValueInUserProf(userId, 6, Convert.ToString(dob));
                    dataContext.uspInsertAttrValueInUserProf(userId, 7, session);
                    dataContext.uspInsertAttrValueInUserProf(userId, 8, "Student");

                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

        }


        public int GetCatIdbyUserId(int userid)
        {
            int catid = 0;

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var categoryId = dataContext.uspGetCatIdFromUserID(userid);

                foreach (uspGetCatIdFromUserIDResult c in categoryId)
                {
                    catid = Convert.ToInt32(c.usertypeId);
                }

                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return catid;
        }

        //returning the html of userprofile form
        public string GetUserProfileFormHtml()
        {

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            string formHtml = "<table>";
            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var profattr = dataContext.uspSelectAllProfileAttributes();


                foreach (uspSelectAllProfileAttributesResult c in profattr)
                {
                    //c.html  = <input type=text size=59
                    formHtml += "<tr>";
                    formHtml += "<td>";
                    formHtml += c.title;
                    formHtml += "</td>";
                    formHtml += "<td>";
                    formHtml += c.html;
                    formHtml += " id='";
                    formHtml += c.fieldId;
                    formHtml += "'";
                    formHtml += "/>";
                    formHtml += "</td>";
                    formHtml += "</tr>";
                }
                formHtml += "</table>";
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return formHtml;
        }

        //getting profile attr id from fieldid list (profileAttrValues)
        public List<int> getProfileattrId(List<string> profileAttrValues)
        {
            List<int> fieldidlist = new List<int>();

            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                for (int i = 0; i < profileAttrValues.Count; i++)
                {
                    var profattrId = dataContext.uspGetProfileAttrIdFromFieldId(profileAttrValues[i]);
                    foreach (uspGetProfileAttrIdFromFieldIdResult c in profattrId)
                    {
                        fieldidlist.Add(c.profattrId);
                    }
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
            return fieldidlist;
        }
        public void saveUserProfile(int userid, List<string> valueList, List<int> profileattridlist)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                for (int i = 0; i < profileattridlist.Count; i++)
                {
                    dataContext.uspInsertAttrValueInUserProf(userid, profileattridlist[i], valueList[i]);
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();

            }
            catch (Exception e)
            {
                string meg = Convert.ToString(e);
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }
        }

        public int AddStudent(String name, String email)
        {
            UserEntries obj = new UserEntries();

            String cName = email.Substring(0, 3);
            int cId = obj.GetCategoryId(cName);
            if (cId == 7) { cId = 1; }
            obj.AddUsers(name, email, cId);
            int uId = obj.GetUserId(email);
            return uId;

        }

        // get forms by User id
        public List<Form> GetFormsByUserId(int uId)
        {
            eFCS_LinqToSqlDataContext dataContext = new eFCS_LinqToSqlDataContext();
            List<Form> formList = new List<Form>();

            try
            {
                dataContext.Connection.Open();
                dataContext.Transaction = dataContext.Connection.BeginTransaction();

                var formNameResults = dataContext.uspGetFormsByUserId(uId);
                Form f = null;
                foreach (uspGetFormsByUserIdResult formNameResult in formNameResults)
                {
                    f = new Form();
                    f.FormId = formNameResult.formId; // circulaiton id
                    f.DocId = formNameResult.docId; //form type
                    f.currentStation = formNameResult.currentstation; //um.. .
                    f.progress = formNameResult.progress; //progress
                    f.subDate = formNameResult.submitionDate; //sub date
                    f.userId = formNameResult.userId; //user id

                    formList.Add(f);
                }
                dataContext.SubmitChanges();
                dataContext.Transaction.Commit();
            }
            catch (Exception)
            {
                dataContext.Transaction.Rollback();
            }
            finally
            {
                dataContext.Connection.Close();
                dataContext.Transaction = null;
            }

            return formList;
        }
    }

}